﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text.RegularExpressions;

namespace Kit
{
    public class PhoneKit
    {
        private const string kOracleServer = "MIS.CCP.COM.TW";
        private const string kOracleUserID = "AC";
        private static byte[] kOracleKey = { 0x54, 0x35, 0x30, 0x30, 0x31, 0x38, 0x35, 0x35 };


        private static string getConnStrMIS()
        {
            string connStrMIS = String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserID,
              System.Text.Encoding.UTF8.GetString(kOracleKey));
            return connStrMIS;
        }


        public static string formatPhoneNo(string PhoneNumber)
        {
            if (!Regex.IsMatch(PhoneNumber, "^[0-9]+$"))
                return PhoneNumber;

            if (PhoneNumber.Length == 5)//台灣分機
                return PhoneNumber.Insert(2, "-");
            if (PhoneNumber.Length == 7 && PhoneNumber.StartsWith("19"))//海外分機
                return PhoneNumber.Insert(4, "-");
            if (PhoneNumber.StartsWith("88") && PhoneNumber.Length == 6)//88第二行動裝置
                return PhoneNumber.Insert(5, "-").Insert(2, "-");
            if (PhoneNumber.StartsWith("88") && PhoneNumber.Length == 7)//88非台北行動裝置
                return PhoneNumber.Insert(4, "-").Insert(2, "-");

            if (PhoneNumber.Length == 10 && PhoneNumber.StartsWith("09"))
            {//台灣手機
                return PhoneNumber.Insert(7, "-").Insert(4, "-");
            }
            if (PhoneNumber.Length == 10)
            {
                //台北、台中市話
                if (Regex.IsMatch(PhoneNumber, "^0[24]"))
                    return PhoneNumber.Insert(6, "-").Insert(2, "-");
            }
            if (PhoneNumber.Length == 9)
            {
                //桃園、雲林、嘉義、台南、高雄市話
                if (Regex.IsMatch(PhoneNumber, "^0[3567]"))
                    return PhoneNumber.Insert(5, "-").Insert(2, "-");
            }

            if (Regex.IsMatch(PhoneNumber, "^1[358][0-9]{9}$"))
            {//中國手機
                return PhoneNumber.Insert(7, "-").Insert(3, "-");
            }
            if (PhoneNumber.Length == 16 && PhoneNumber.StartsWith("009861"))
            {//中國009手機
                return PhoneNumber.Insert(12, "-").Insert(8, "-").Insert(5, "-").Insert(3, "-");
            }
            if (PhoneNumber.Length > 11 && PhoneNumber.StartsWith("00986"))
            {//中國009電話
                return PhoneNumber.Insert(5, "-").Insert(3, "-");
            }
            if (PhoneNumber.Length == 15 && Regex.IsMatch(PhoneNumber, "^00[29]81[789]0[0-9]{8}$"))
            {//日本手機
                return PhoneNumber.Insert(11, "-").Insert(7, "-").Insert(5, "-").Insert(3, "-");
            }
            return PhoneNumber;
        }


        public static string getOracleVCTelName(string PhoneNumber)
        {
            string resultStr = formatPhoneNo(PhoneNumber);
            OracleConnection connMIS = new OracleConnection(getConnStrMIS());

            try
            {
                connMIS.Open();

                // 【01】台北市話要把'02'拿掉
                string tweakNumber = PhoneNumber;
                if (Regex.IsMatch(tweakNumber, "^02[0-9]{8}$"))
                    tweakNumber = tweakNumber.Remove(0, 2);

                string selStr = "SELECT TYPE, VC_NO, CONTACT FROM VC_TEL"
                  + " WHERE TEL_NO IN (:TEL_NO1, :TEL_NO2)"
                  + " AND (VC_NO IS NOT NULL OR CONTACT IS NOT NULL)";
                OracleCommand selCmd = new OracleCommand(selStr, connMIS);
                selCmd.Parameters.Add("TEL_NO1", OracleType.VarChar).Value = tweakNumber;
                selCmd.Parameters.Add("TEL_NO2", OracleType.VarChar).Value = PhoneNumber;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow firstRow = dataSet.Tables[0].Rows[0];
                    string contactName = firstRow["CONTACT"].ToString();
                    string phoneType = firstRow["TYPE"].ToString();
                    string companyKey = firstRow["VC_NO"].ToString();
                    string companyName = "";

                    // 【02】抓取公司名稱
                    if (phoneType == "V")
                    {
                        string selStrV = "SELECT VENDOR_FULL FROM VENDOR WHERE VENDOR_ABN=:VENDOR_ABN";
                        OracleCommand selCmdV = new OracleCommand(selStrV, connMIS);
                        selCmdV.Parameters.Add("VENDOR_ABN", OracleType.VarChar).Value = companyKey;
                        companyName = Convert.ToString(selCmdV.ExecuteScalar());
                    }
                    else if (phoneType == "C")
                    {
                        string selStrC = "SELECT CUST_NAME FROM MSF_CTM WHERE CUST_NOA=:CUST_NOA";
                        OracleCommand selCmdC = new OracleCommand(selStrC, connMIS);
                        selCmdC.Parameters.Add("CUST_NOA", OracleType.VarChar).Value = companyKey;
                        companyName = Convert.ToString(selCmdC.ExecuteScalar());
                    }
                    string shortCompName = ChrisKit.trimCompName(companyName);


                    // 【03】組合聯絡人、公司名稱
                    if (!string.IsNullOrEmpty(shortCompName) && !string.IsNullOrEmpty(contactName))
                        resultStr = string.Format("{0}[{1}] {2}", contactName.Trim(), formatPhoneNo(PhoneNumber), shortCompName);
                    else if (!string.IsNullOrEmpty(shortCompName))
                        resultStr = string.Format("{0}[{1}]", shortCompName, formatPhoneNo(PhoneNumber));
                    else if (!string.IsNullOrEmpty(contactName))
                        resultStr = string.Format("{0}[{1}]", contactName.Trim(), formatPhoneNo(PhoneNumber));
                }
            }
            finally
            {
                connMIS.Close();
            }
            return resultStr;
        }


        public static string getCCPPhoneName(string phoneNumber)
        {
            PhoneInfo phoneInfo = new PhoneInfo(phoneNumber);
            if (string.IsNullOrEmpty(phoneInfo.FullName))
                return phoneInfo.PhoneNumber;
            else
                return string.Format("{0}", phoneInfo.FullName);
        }


        public static string getPhoneInfo(string phoneNumber)
        {
            PhoneInfo phoneInfo = new PhoneInfo(phoneNumber);
            if (!string.IsNullOrEmpty(phoneInfo.FullName))
                return string.Format("{0}[{1}] {2}", phoneInfo.FullName, phoneInfo.DisplayNumber, phoneInfo.SectionName);
            else if (Regex.IsMatch(phoneNumber, "^194[0-9]{4}$"))
                return string.Format("{0}[{1}]", MySqlKit.getFullName(phoneNumber), phoneInfo.DisplayNumber);
            else if (phoneNumber.Length <= 7)
                return phoneInfo.DisplayNumber;
            else
                return getOracleVCTelName(phoneInfo.PhoneNumber);
        }


        public static string getCallerInfo(string callerNumber, DateTime callTime, string ringerNumber)
        {
            string resultStr = "Call: ";

            // 檢查是否為 MSG 的 Web Call
            if (Regex.IsMatch(callerNumber, "^[0-9]{3,7}_To_[0-9]{3,20}$"))
            {
                string[] separator = { "_To_" };
                string[] phoneNumbers = callerNumber.Split(separator, StringSplitOptions.RemoveEmptyEntries);
                if (phoneNumbers.Length == 2)
                {
                    if (ringerNumber == "88" + phoneNumbers[0] || ringerNumber == phoneNumbers[0])
                    {//發話方
                        resultStr += "MSG Call【" + getPhoneInfo(phoneNumbers[1]) + "】";
                        return resultStr + callTime.ToString(" (M/d HH:mm)");
                    }
                    else if (ringerNumber == "88" + phoneNumbers[1] || ringerNumber == phoneNumbers[1])
                    {//受話方
                        return getCallerInfo(phoneNumbers[0], callTime, ringerNumber);
                    }
                }
            }

            resultStr += getPhoneInfo(callerNumber);
            return resultStr + callTime.ToString(" (M/d HH:mm)");
        }
    }

    public class PhoneInfo
    {
        //private const string kPhoneBookFile = @"R:\Temp\CCPPhoneBook.sqlite";//mi953win7
        //private const string kPhoneBookFile = @"D:\CCPSIP\Push\CCPPhoneBook.sqlite";//ios.ccp

        private const string kOracleServerIsender = "ISENDER.CCP.COM.TW";
        private const string kOracleUserIdIsender = "ISENDER";
        private static byte[] kOracleKeyIsender = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private static string GetConnStrIsender()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServerIsender, kOracleUserIdIsender, System.Text.Encoding.UTF8.GetString(kOracleKeyIsender));
        }

        private string _PhoneNumber;
        private string _DisplayNumber;
        private string _FullName = "";
        private string _SectionName = "";
        private string _PPSCode = "";

        public string PhoneNumber { get { return this._PhoneNumber; } }
        public string DisplayNumber { get { return this._DisplayNumber; } }
        public string FullName { get { return this._FullName; } }
        public string SectionName { get { return this._SectionName; } }
        public string PPSCode { get { return this._PPSCode; } }


        public PhoneInfo(string PhoneNo)
        {
            _PhoneNumber = PhoneNo.Trim();
            _DisplayNumber = PhoneKit.formatPhoneNo(_PhoneNumber);

            // 【01】解析電話號碼
            //
            string tweakNumber = _PhoneNumber;
            if (Regex.IsMatch(tweakNumber, "^88[0-9]{3,4}$"))
                tweakNumber = "62" + tweakNumber.Substring(2, 3);
            else if (Regex.IsMatch(tweakNumber, "^886[2-8][1-9][0-9]{2}$"))
            {
                tweakNumber = tweakNumber.Substring(2, 5);
            }
            else if (tweakNumber.Length == 4)
            {
                if (tweakNumber.StartsWith("222"))
                    tweakNumber = "62222";
                else if (Regex.IsMatch(tweakNumber, "^23[3-9]1$"))
                    tweakNumber = "62" + tweakNumber.Substring(0, 3);
            }
            else if (Regex.IsMatch(tweakNumber, "^0277023[1-9][0-9]{2}$"))
                tweakNumber = "62" + tweakNumber.Substring(7, 3);
            else if (tweakNumber == "27140")
            {
                _FullName = "外線跳接";
            }

            if (tweakNumber.Length <= 7)
            {
                //SQLiteConnection connSQLite = new SQLiteConnection(@"Data Source=" + kPhoneBookFile);
                OracleConnection connOracle = new OracleConnection(GetConnStrIsender());

                try
                {
                    //connSQLite.Open();
                    connOracle.Open();

                    // 【02】讀取電話表資訊
                    // TODO: 2014/12/10 加上判斷DISPLAY = 'N'的時候 只顯示電話號碼，不顯示名字
                    string cmd_all = "SELECT FULL_NAME, SECTION_NAME, PPS_CODE FROM PHONE_BOOK"
                                   + " WHERE AREA_CODE||LOCAL_NUMBER=:callerNumber1"
                                   + " AND DISPLAY = 'Y'"
                                   + "    OR AREA_CODE||LOCAL_NUMBER=:callerNumber2"
                                   + " AND DISPLAY = 'Y'";

                    OracleCommand cmd = new OracleCommand(cmd_all, connOracle);

                    if (tweakNumber.StartsWith("6") && tweakNumber.Length <= 5)
                    {
                        cmd.Parameters.Add(":callerNumber1", OracleType.VarChar).Value = tweakNumber;
                        cmd.Parameters.Add(":callerNumber2", OracleType.VarChar).Value = tweakNumber;
                    }
                    else if (tweakNumber.Length == 3 || tweakNumber.Length == 4)
                    {
                        cmd.Parameters.Add(":callerNumber1", OracleType.VarChar).Value = "62" + tweakNumber;
                        cmd.Parameters.Add(":callerNumber2", OracleType.VarChar).Value = "50" + tweakNumber;
                    }
                    else if (tweakNumber.Length == 5)
                    {
                        cmd.Parameters.Add(":callerNumber1", OracleType.VarChar).Value = tweakNumber;
                        cmd.Parameters.Add(":callerNumber2", OracleType.VarChar).Value = "19" + tweakNumber;
                    }
                    else
                    {
                        cmd.Parameters.Add(":callerNumber1", OracleType.VarChar).Value = tweakNumber;
                        cmd.Parameters.Add(":callerNumber2", OracleType.VarChar).Value = tweakNumber;
                    }
                    OracleDataAdapter adapterPhone = new OracleDataAdapter(cmd);
                    DataSet dataSetPhone = new DataSet();
                    adapterPhone.Fill(dataSetPhone);

                    DataRowCollection resultRows = dataSetPhone.Tables[0].Rows;
                    if (resultRows.Count > 0)
                    {
                        DataRow firstRow = resultRows[0];

                        // 【03】相同號碼多筆名稱合併
                        //
                        _FullName = firstRow["FULL_NAME"].ToString();
                        _SectionName = firstRow["SECTION_NAME"].ToString();
                        _PPSCode = firstRow["PPS_CODE"].ToString();

                        if (resultRows.Count > 1)
                        {
                            _PPSCode = "";
                            for (int i = 1; i < resultRows.Count; i++)
                            {
                                DataRow row = resultRows[i];
                                string FullNameExtra = row["FULL_NAME"].ToString();
                                _FullName += "/" + FullNameExtra;
                            }
                        }
                    }
                }
                finally
                {
                    connOracle.Close();
                }
            }
        }
    }

}